package com.blue.waffle.iwc.domains.helpers;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Random;

import org.apache.commons.lang3.StringUtils;

import com.blue.waffle.iwc.domains.Level;
import com.blue.waffle.iwc.domains.Profile;
import com.blue.waffle.iwc.domains.Word;
import com.blue.waffle.iwc.utils.LruCache;

public class WordHelper  implements DataHelper<Word>{
	
	private Connection connection;
	private static final String TABLE_NAME="word";
	private PreparedStatement selectStatement;
	private PreparedStatement updateStatement;
	private PreparedStatement insertStatement;
	private PreparedStatement deleteStatement;
	private static final String SELECT_ALL_SQL="SELECT * FROM "+TABLE_NAME;
	private static final String SELECT_BY_ID_SQL="SELECT * FROM "+TABLE_NAME+" WHERE id=?";
	private static LruCache<Integer, Word> objectCache;
	private static final String INSERT_SQL="INSERT INTO "+TABLE_NAME+" (word,definition,picture) VALUES (?,?,?)";
	private static final String UPDATE_SQL="UPDATE "+TABLE_NAME+" SET word=?, definition=?, picture=? WHERE id=?";
	private static final String DELETE_SQL="DELETE FROM "+TABLE_NAME+" WHERE id=?";

	public WordHelper(Connection connection) {
		this.connection = connection;
		if (objectCache==null) {
			objectCache = new LruCache<Integer, Word>(10);
		}
	}
	
	@Override
	public boolean isClosed() throws SQLException {
		if (connection != null) {
			return connection.isClosed();
		}
		return false;
	}
	
	@Override
	public void close() throws SQLException {
		if (connection != null) {
			if (!connection.isClosed()) {
				connection.close();
			}
			connection = null;
		}
	}
	
	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	@Override
	public List<Word> selectAll() throws SQLException {
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(SELECT_ALL_SQL);
		List<Word> list = new ArrayList<Word>();
		while(resultSet.next()) {
			list.add(getObject(resultSet));
		}
		resultSet.close();
		return list;
	}

	@Override
	public List<Word> select(String sql, Object[] para) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}
	
	public Word findById(int id) throws SQLException{
		selectStatement = connection.prepareStatement(SELECT_BY_ID_SQL);
		selectStatement.setInt(1, id);
		ResultSet resultSet = selectStatement.executeQuery();
		Word model=null;
		while(resultSet.next()) {
			model=getObject(resultSet);
		}
		resultSet.close();
		selectStatement.close();
		
		return model;
	}

	@Override
	public void insert(Word model) throws SQLException {
		insertStatement = connection.prepareStatement(INSERT_SQL);
		insertStatement.setString(1, model.getWord());
		insertStatement.setString(2, model.getDefinition());
		insertStatement.setString(3, model.getPictureFileName());
		insertStatement.executeUpdate();
		Statement statement = connection.createStatement();
		ResultSet keyGenerated = statement.executeQuery("SELECT last_insert_rowid()");
		if(keyGenerated.next()) {
			int id = keyGenerated.getInt(1);
			model.setId(id);
			objectCache.put(id, model);
		}
		keyGenerated.close();
		insertStatement.close();
	}

	@Override
	public void update(Word model) throws SQLException {
		updateStatement = connection.prepareStatement(UPDATE_SQL);
		updateStatement.setString(1, model.getWord());
		updateStatement.setString(2, model.getDefinition());
		updateStatement.setString(3, model.getPictureFileName());
		updateStatement.setInt(4, model.getId());
		updateStatement.executeUpdate();
		objectCache.put(model.getId(), model);
		updateStatement.close();
	}

	@Override
	public void delete(Word model) throws SQLException {
		deleteStatement = connection.prepareStatement(DELETE_SQL);
		deleteStatement.setInt(1, model.getId());
		deleteStatement.executeUpdate();
		objectCache.remove(model.getId());
		deleteStatement.close();
	}

	@Override
	public int count(String sql) throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int count() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Word getObject(ResultSet resultSet) throws SQLException {
		int id=resultSet.getInt(Profile.ID);
		Word word=objectCache.get(id);
		if (word==null) {
			word=new Word();
			word.setId(id);
			word.setWord(resultSet.getString(Word.WORD));
			word.setDefinition(resultSet.getString(Word.DEFINITION));
			objectCache.put(id, word);
		}else {
			word.setWord(resultSet.getString(Word.WORD));
			word.setDefinition(resultSet.getString(Word.DEFINITION));	
		}
		return word;
	}
	
	/**
	 * A utility that insert words from a csv file. It internally performs a batch insert in transaction.
	 * @param file - the file handle of the csv file
	 * @throws IOException
	 * @throws SQLException
	 */
	public void insertWordsFromCSV(Reader file) throws IOException, SQLException{
		CSVHelper csvHelper=new CSVHelper(file);
		ArrayList<String[]> wordData=(ArrayList<String[]>) csvHelper.getFileData();
		if (wordData!=null && !wordData.isEmpty()) {
			ArrayList<Word> words=new ArrayList<Word>();
			
			for (String[] wordStrings : wordData) {
				String wordString=wordStrings[0];
				String definitionString=wordStrings[1];
				
				if (!StringUtils.isEmpty(wordString) && !StringUtils.containsWhitespace(wordString)) {
					if (!StringUtils.isEmpty(definitionString)&& StringUtils.containsWhitespace(definitionString)) {
						Word word=new Word();
						word.setWord(wordString);
						word.setDefinition(definitionString);
						words.add(word);
						continue;
					}
				}
				System.out.println("Missed row data: "+wordStrings.toString());
			}
			
			if (words.size()>0) {
				insertWords(words);
			}
		}
		csvHelper.dispose();
		csvHelper=null;
	}
	
	/**
	 * Performs a batch insert in transaction by taking a list of {@link Word words}.
	 * @param words
	 * @throws SQLException
	 */
	public void insertWords(List<Word> words) throws SQLException{
		connection.setAutoCommit(false);
		insertStatement = connection.prepareStatement(INSERT_SQL);
		for (Word model : words) {
			insertStatement.setString(1, model.getWord());
			insertStatement.setString(2, model.getDefinition());
			insertStatement.setString(3, model.getPictureFileName());
			insertStatement.executeUpdate();
		}
		connection.commit();
		insertStatement.close();
		connection.setAutoCommit(true);
	}

}
